package control;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import model.Divisi;
import model.KategoriMenu;
import model.Menu;
import model.Pegawai;

public class AdminKontrol {

    private Connection conn;

    /**
     *
     * @param koneksi
     */
    public AdminKontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static AdminKontrol getKoneksiKategori() throws SQLException {
        AdminKontrol kon;
        kon = new AdminKontrol(OraConnection.getKoneksi());
        return kon;
    }

    public List<Menu> tampilMenu() throws SQLException {
        List<Menu> menuList = new ArrayList<Menu>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT menu.idmenu, menu.namamenu, menu.hargamenu, "
            + "kategorimenu.idkat, kategorimenu.namakat FROM menu, kategorimenu "
            + "where menu.kategori=kategorimenu.idkat");
        while (rs.next()) {
            Menu menu = new Menu();
            KategoriMenu kat = new KategoriMenu();
            kat.setIdKategori(rs.getString("idkat"));
            kat.setNamaKategori(rs.getString("namakat"));
            menu.setIdMenu(rs.getString("idmenu"));
            menu.setNamaMenu(rs.getString("namamenu"));
            menu.setHargaMenu(rs.getLong("hargamenu"));
            menu.setKategori(kat);
            menuList.add(menu);
        }
        return menuList;
    }
    
    public String carikodemenu(String kode) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        long kodebarang=0;
        String a;
        try {
            conn.setAutoCommit(false);
            String sql = "select max(idmenu) from menu where kategori like '" + kode + "'";
            statement = conn.prepareStatement(sql);
            result = statement.executeQuery();
            while (result.next()) {
                a= result.getString("max(idmenu)");
                if(a==null){
                    kodebarang=0;
                    break;
                }
                String b = a.substring(3,6);
                kodebarang=Long.parseLong(b);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        kodebarang= kodebarang + 1;
        String fix = String.valueOf(kodebarang);
        return fix;
    }
    
    public void insertDataMenu(Menu menu) throws SQLException {

        String idMenu = menu.getIdMenu();
        String namaMenu = menu.getNamaMenu();

        long hargaMenu = menu.getHargaMenu();
        String katMenu = menu.getKategori().getIdKategori();
        String sql = "insert into menu (idMenu, namaMenu,hargaMenu,kategori)"
                + " values ('" + idMenu + "','" + namaMenu + "','" + hargaMenu + "','" + katMenu + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void updateDataMenu(Menu menu) throws SQLException {
        String idMenu = menu.getIdMenu();
        String namaMenu = menu.getNamaMenu();
        long hargaMenu = menu.getHargaMenu();
        String sql = "update menu set hargaMenu = '" + hargaMenu + "', "
                + "namaMenu = '" + namaMenu + "' "
                + " where idMenu = '" + idMenu + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deleteMenu(Menu menu) throws SQLException {
        String idMenu = menu.getIdMenu();
        String sql = "delete from menu "
                + "where idMenu= '" + idMenu + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    
    public List<Pegawai> tampilPegawai() throws SQLException {
        List<Pegawai> pegList = new ArrayList<Pegawai>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT pegawai.idpegawai, pegawai.namapegawai, pegawai.username, "
                + "pegawai.password, divisi.iddivisi, divisi.namadivisi FROM pegawai, divisi "
                + "where pegawai.divisi=divisi.iddivisi");
        while (rs.next()) {
            Pegawai peg = new Pegawai();
            Divisi div = new Divisi();
            div.setIdDivisi(rs.getString("iddivisi"));
            div.setNamaDivisi(rs.getString("namadivisi"));
            peg.setIdPegawai(rs.getString("idpegawai"));
            peg.setNamaPegawai(rs.getString("namapegawai"));
            peg.setUsername(rs.getString("username"));
            peg.setPassword(rs.getString("password"));
            peg.setDivisi(div);
            pegList.add(peg);
        }
        return pegList;
    }
    
    public String carikodepegawai(String kode) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        long kodepeg=0;
        String a;
        try {
            conn.setAutoCommit(false);
            String sql = "select max(idpegawai) from pegawai where divisi like '" + kode + "'";
            statement = conn.prepareStatement(sql);
            result = statement.executeQuery();
            while (result.next()) {
                a= result.getString("max(idpegawai)");
                if(a==null){
                    kodepeg=0;
                    break;
                }
                String b = a.substring(3,6);
                kodepeg=Long.parseLong(b);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        kodepeg= kodepeg + 1;
        String fix = String.valueOf(kodepeg);
        return fix;
    }

    public void insertPegawai(Pegawai peg) throws SQLException {
        String idPegawai = peg.getIdPegawai();
        String namaPegawai = peg.getNamaPegawai();
        String username = peg.getUsername();
        String password = peg.getPassword();
        String idDivisi = peg.getDivisi().getIdDivisi();
        String sql = "insert into Pegawai (idPegawai, namaPegawai, username, password, divisi)"
                + " values ('" + idPegawai + "','" + namaPegawai + "','" + username + "','" + password + "','" + idDivisi + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void updatePegawai(Pegawai peg) throws SQLException {
        String idPegawai = peg.getIdPegawai();
        String namaPegawai = peg.getNamaPegawai();
        String username = peg.getUsername();
        String password = peg.getPassword();
        String idDivisi = peg.getDivisi().getIdDivisi();
        String sql = "update Pegawai set namaPegawai = '" + namaPegawai + "' "
                + "username = '" + username + "' "
                + "password = '" + password + "' "
                + "idDivisi = '" + idDivisi + "' "
                + " where idPegawai= '" + idPegawai + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePegawai(Pegawai peg) throws SQLException {
        String idPegawai = peg.getIdPegawai();
        String sql = "delete from Pegawai "
                + " where idPegawai= '" + idPegawai + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public List<KategoriMenu> tampilKategoriMenu() throws SQLException {
        List<KategoriMenu> katList = new ArrayList<KategoriMenu>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from kategorimenu");
        while (rs.next()) {
            KategoriMenu kat = new KategoriMenu();
            kat.setIdKategori(rs.getString("idkat"));
            kat.setNamaKategori(rs.getString("namakat"));
            katList.add(kat);
        }
        return katList;
    }
    public String carikodekategori() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        long kodekat = 0;
        String a = "", fix = "";
        try {
            conn.setAutoCommit(false);
            String sql = "select max(idkat) from kategorimenu ";
            statement = conn.prepareStatement(sql);
            result = statement.executeQuery();
            while (result.next()) {
                a = result.getString("max(idkat)");
                if (a == null) {
                    kodekat = 0;
                    break;
                }
                kodekat = Long.parseLong(a);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        kodekat = kodekat + 1;
        String tmbh1 = String.valueOf(kodekat);
        if (kodekat < 10) {
            String a1 = "00";
            fix = a1 + tmbh1;
        } else if (kodekat < 100 && kodekat > 9) {
            String b = "0";
            fix = b + tmbh1;
        } else if (kodekat > 99 && kodekat < 1000) {
            fix = String.valueOf(kodekat);
        }
        return fix;
    }

    public void insertKategoriMenu(KategoriMenu katmenu) throws SQLException {
        String idKat = katmenu.getIdKategori();
        String namaKat = katmenu.getNamaKategori();
        String sql = "insert into KategoriMenu (idKat, namaKat)"
                + " values ('" + idKat + "','" + namaKat + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void updateKategoriMenu(KategoriMenu katmenu) throws SQLException {
        String idKat = katmenu.getIdKategori();
        String namaKat = katmenu.getNamaKategori();
        String sql = "update KategoriMenu set namaKat= '" + namaKat + "' "
                + "where  idKat= '" + idKat + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deleteKategoriMenu(KategoriMenu katmenu) throws SQLException {
        String idKat = katmenu.getIdKategori();
        String sql = "delete from kategorimenu where idkat='" + idKat + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public String carikodedivisi() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        long kodediv = 0;
        String a = "", fix = "";
        try {
            conn.setAutoCommit(false);
            String sql = "select max(iddivisi) from divisi";
            statement = conn.prepareStatement(sql);
            result = statement.executeQuery();
            while (result.next()) {
                a = result.getString("max(iddivisi)");
                if (a == null) {
                    kodediv = 0;
                    break;
                }
                kodediv = Long.parseLong(a);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        kodediv = kodediv + 1;
        String tmbh1 = String.valueOf(kodediv);
        if (kodediv < 10) {
            String a1 = "00";
            fix = a1 + tmbh1;
        } else if (kodediv < 100 && kodediv > 9) {
            String b = "0";
            fix = b + tmbh1;
        } else if (kodediv > 99 && kodediv < 1000) {
            fix = String.valueOf(kodediv);
        }
        return fix;
    }

    public List<Divisi> tampilDivisiPegawai() throws SQLException {
        List<Divisi> divisiList = new ArrayList<Divisi>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from divisi");
        while (rs.next()) {
            Divisi divisi = new Divisi();
            divisi.setIdDivisi(rs.getString("iddivisi"));
            divisi.setNamaDivisi(rs.getString("namadivisi"));
            divisiList.add(divisi);
        }
        return divisiList;
    }

    public void insertDivisiPegawai(Divisi div) throws SQLException {
        String idDivisi = div.getIdDivisi();
        String namaDivisi = div.getNamaDivisi();
        String sql = "insert into Divisi (idDivisi, namaDivisi)"
                + " values ('" + idDivisi + "','" + namaDivisi + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void updateDivisiPegawai(Divisi div) throws SQLException {
        String idDivisi = div.getIdDivisi();
        String namaDivisi = div.getNamaDivisi();
        String sql = "update Divisi set namaDivisi ='" + namaDivisi + "' "
                + "where idDivisi = '" + idDivisi + "' ";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();

    }

    public void deleteDivisiPegawai(Divisi div) throws SQLException {
        String idDivisi = div.getIdDivisi();
        String sql = "delete from Divisi "
                + "where idDivisi='" + idDivisi + "' ";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
}
